﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Data.OleDb;

namespace DrugsFilter
{
    public partial class DrugsFilter : Form
    {
        public DrugsFilter()
        {
            InitializeComponent();

        }
        public struct sp_params
        {
            public string name;
            public object value;
        }

        public string namechcol = "Флаг";
        private CurrencyManager cm;
        //private string arch = "";
        public DataTable[] dt_rezult = new DataTable[9];
        public DataTable dt_br = new DataTable();
        public SqlCommand sqlcom = new SqlCommand();
        public SqlConnection sqlconn = new SqlConnection(@"Server=sdata2;Database=PharmOLAPDW;Integrated Security=True");
        public DataTable dt_drug_tmp = new DataTable();
        public DataTable dt_drug = new DataTable();
        public DataTable dt_brand = new DataTable();
        public DataTable dt_tree = new DataTable();
        private Guid guid;
        public StorProcParam[] stprparam;
        public bool rb = false;

        public DataTable[] SqlProcExecWithQuery(SqlConnection con, string procname, int timeout, sp_params[] param)
        {
            DataTable[] dt = new DataTable[0];
            try
            {
                if (con.State == ConnectionState.Closed) con.Open();
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandTimeout = timeout;
                com.CommandText = procname;

                if (param != null)
                {
                    foreach (sp_params p in param)
                    {
                        com.Parameters.AddWithValue(p.name, p.value);
                    }
                }
                SqlDataReader dr = com.ExecuteReader();

                while (dr.HasRows)
                {
                    Array.Resize(ref dt, dt.Length + 1);
                    dt[dt.Length - 1] = new DataTable();
                    dt[dt.Length - 1].Load(dr);
                    if (dt[dt.Length - 1].Columns.Count > 2) dt[dt.Length - 1].TableName = dt[dt.Length - 1].Rows[0][1].ToString();
                    if (dr.IsClosed) break;
                }
                dr.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }


        private void Form2_Load(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView1.DataSource != null)
                {
                    cm = (CurrencyManager)dataGridView1.BindingContext[dataGridView1.DataSource];
                    dataGridView1.Sort(dataGridView1.Columns["Товар"], ListSortDirection.Ascending);
                    HideShowRows();
                }
                if (dt_drug.Columns.Count > 0)
                {
                    dt_drug_tmp.Clone();
                }
                sqlcom = SelDrugs(sqlcom, sqlconn);
                LoadFiltersCB(sqlconn, comboBox1);
                //LoadTreeView(treeView1);
                dt_rezult = SqlProcExecWithQuery(sqlconn, "pas_AllAttrDrugs", 300, null);
                for (int i = 0; i < 9; i++)
                {
                    dt_rezult[i].Columns[0].ReadOnly = false;
                }
                comboBox2.DataSource = dt_rezult[0].DefaultView;
                comboBox2.DisplayMember = "DrugName";               // столбец для отображения
                comboBox2.ValueMember = "id";                       // столбец для счёта
                comboBox3.DataSource = dt_rezult[1].DefaultView;
                comboBox3.DisplayMember = "Country";                // столбец для отображения
                comboBox3.ValueMember = "id";                       // столбец для счёта
                comboBox4.DataSource = dt_rezult[2].DefaultView;
                comboBox4.DisplayMember = "Producer";               // столбец для отображения
                comboBox4.ValueMember = "id";                       // столбец для счёта
                comboBox5.DataSource = dt_rezult[3].DefaultView;
                comboBox5.DisplayMember = "OrderGroup";             // столбец для отображения
                comboBox5.ValueMember = "id";                       // столбец для счёта
                comboBox6.DataSource = dt_rezult[4].DefaultView;
                comboBox6.DisplayMember = "SpecGroup";              // столбец для отображения
                comboBox6.ValueMember = "id";                       // столбец для счёта
                comboBox7.DataSource = dt_rezult[5].DefaultView;
                comboBox7.DisplayMember = "CategName";              // столбец для отображения
                comboBox7.ValueMember = "id";                       // столбец для счёта
                comboBox8.DataSource = dt_rezult[6].DefaultView;
                comboBox8.DisplayMember = "DepartName";             // столбец для отображения
                comboBox8.ValueMember = "id";                       // столбец для счёта
                comboBox9.DataSource = dt_rezult[7].DefaultView;
                comboBox9.DisplayMember = "MNN";                   // столбец для отображения
                comboBox9.ValueMember = "id";                      // столбец для счёта
                comboBox10.DataSource = dt_rezult[8].DefaultView;
                comboBox10.DisplayMember = "TorgTech";              // столбец для отображения
                comboBox10.ValueMember = "id";                      // столбец для счёта

                comboBox2.SelectedIndex = -1;
                comboBox3.SelectedIndex = -1;
                comboBox4.SelectedIndex = -1;
                comboBox5.SelectedIndex = -1;
                comboBox6.SelectedIndex = -1;
                comboBox7.SelectedIndex = -1;
                comboBox8.SelectedIndex = -1;
                comboBox9.SelectedIndex = -1;
                comboBox10.SelectedIndex = -1;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            //arch = ""; //все
            //HideShowRows();
            EnterFilter();
        }

        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            //arch = "да"; //архивные товары
            //HideShowRows();
            EnterFilter();
        }

        private void radioButton3_CheckedChanged(object sender, EventArgs e)
        {
            //arch = "нет"; //неархивные товары
            //HideShowRows();
            EnterFilter();
        }

        private void textBox1_Enter(object sender, EventArgs e)
        {
            HideShowRows();
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            HideShowRows();
        }

        private void HideShowRows()
        {
            cm = (CurrencyManager)dataGridView1.BindingContext[dataGridView1.DataSource];
            cm.SuspendBinding();
            int select = 0;
            int filter = 0;
            foreach (DataGridViewRow r in dataGridView1.Rows)
            {
                r.Visible = true;
                r.DefaultCellStyle.BackColor = SystemColors.ControlLightLight;

                //if (arch != "")
                //    if (Convert.ToString(dataGridView1["Архивный", r.Index].Value) != arch) r.Visible = false;

                if (dataGridView1.SortedColumn != null)
                    if (!Regex.Match(Convert.ToString(dataGridView1[dataGridView1.SortedColumn.Name, r.Index].Value), textBox1.Text, RegexOptions.IgnoreCase).Success)
                        r.Visible = false;

                if (Convert.ToBoolean(dataGridView1[namechcol, r.Index].Value))
                {
                    select++;
                    r.DefaultCellStyle.BackColor = Color.Red;
                }
                if (r.Visible) filter++;
            }
            cm.ResumeBinding();
            ToolStripItemsUpd(dataGridView1.Rows.Count, select, filter);
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            HideShowRows();
        }

        private void dataGridView1_Sorted(object sender, EventArgs e)
        {
            HideShowRows();
        }

        private void ToolStripItemsUpd(int all, int select, int filter)
        {
            statusStrip1.Items.Clear();
            statusStrip1.Items.Add("Отфильтровано " + filter.ToString());
            statusStrip1.Items.Add("Выбранно " + select.ToString());
            statusStrip1.Items.Add("Всего выбранно " + dt_drug_tmp.Rows.Count);
        }

        private void dataGridView1_CurrentCellDirtyStateChanged(object sender, EventArgs e)
        {
            if (dataGridView1.IsCurrentCellDirty)
                dataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit);
        }

        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                int i = 0;
                if (dt_drug_tmp.Columns.Count == 0)
                {
                    dt_drug_tmp = dt_drug.Clone();
                }
                if (e.ColumnIndex == dataGridView1.Columns[namechcol].Index)
                     if ((bool)dataGridView1[e.ColumnIndex, e.RowIndex].Value == true)
                        {
                            dataGridView1.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.Red;
                            DataRow dr = dt_drug_tmp.NewRow();
                            foreach (DataColumn dc in dt_drug.Columns)
                            {
                                dr[dc.ColumnName] = dataGridView1[dc.ColumnName, e.RowIndex].Value;
                            }
                            dt_drug_tmp.Rows.Add(dr);
                            i++;
                        }
                        else
                        {
                            dataGridView1.Rows[e.RowIndex].DefaultCellStyle.BackColor = SystemColors.ControlLightLight;
                            foreach (DataRow dr in dt_drug_tmp.Rows)
                            {
                                if (dr.RowState != DataRowState.Deleted)
                                    if (Convert.ToInt32(dr["Код"]) == Convert.ToInt32(dataGridView1["Код", e.RowIndex].Value))
                                    {
                                        dt_drug_tmp.Rows.Remove(dr);
                                        break;
                                    }
                            }
                            i--;
                        }
                    statusStrip1.Items[statusStrip1.Items.Count - 2].Text = "Выбранно " + (Convert.ToInt32(statusStrip1.Items[statusStrip1.Items.Count - 2].Text.Replace("Выбранно ", "")) + i).ToString();
                    statusStrip1.Items[statusStrip1.Items.Count - 1].Text = "Всего выбранно " + (Convert.ToInt32(statusStrip1.Items[statusStrip1.Items.Count - 1].Text.Replace("Всего выбранно ", "")) + i).ToString();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //string s = "0";
            //try
            //{
                OpenFileDialog opf = new OpenFileDialog();
                opf.Filter = "Файлы Excel (1997-2007)|*.XLSX;*.XLS";
                if (opf.ShowDialog() == DialogResult.OK)
                {

                    //try
                    //{                     
                        string filename = opf.FileName;
                        string ConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 8.0;", filename);
                        DataSet ds = new DataSet("EXCEL");
                        OleDbConnection cn = new OleDbConnection(ConStr);
                        
 
                        if (cn.State == ConnectionState.Closed) cn.Open();
                        DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { }); //{ null, null, null, null }
                        string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
                        string select = String.Format("SELECT * FROM [{0}]", sheet1);
                        OleDbDataAdapter ad = new OleDbDataAdapter(select, cn);
                        ad.Fill(ds);
                        DataTable tb = ds.Tables[0];
                        cn.Close();
                        
                        //object[] obj;
                        //if(tb != null)
                        //{
                        //    obj = new object[tb.Rows.Count];
                        //    for(int i = 0; i < tb.Rows.Count; i++)
                        //        obj[i] = Convert.ToInt32(tb.Rows[i]);
                        //}

                        SqlCommand myCommand = sqlconn.CreateCommand();
                        myCommand.CommandType = CommandType.StoredProcedure;
                        myCommand.CommandText = @"pas_GuidNatureCodeINS";
                        myCommand.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
                        myCommand.Parameters.Add("@code", SqlDbType.Int);
                        myCommand.Parameters.Add("@del", SqlDbType.Bit);
                        myCommand.Parameters.Add("@nature", SqlDbType.VarChar, 5);
                        
                        if(sqlconn.State == ConnectionState.Closed) sqlconn.Open();
                        
                        myCommand.Parameters["@guid"].Value = guid;
                        myCommand.Parameters["@code"].Value = null;
                        myCommand.Parameters["@del"].Value = true;
                        myCommand.Parameters["@nature"].Value = null;
                        myCommand.ExecuteNonQuery();
                        
                        guid = Guid.NewGuid();
                        
                        if (tb != null)
                            foreach (DataRow dr in tb.Rows)
                            {
                                if (dr[0] != DBNull.Value)
                                {
                                    myCommand.Parameters["@guid"].Value = guid;
                                    myCommand.Parameters["@code"].Value = Convert.ToInt32(dr[0]); ;
                                    myCommand.Parameters["@del"].Value = false;
                                    myCommand.Parameters["@nature"].Value = "drug";
                                    myCommand.ExecuteNonQuery();
                                }
                            }
                        
                        sqlcom.Parameters["@guid"].Value = guid;
                        dt_drug_tmp.Clear();
                        dt_drug_tmp.Load(sqlcom.ExecuteReader());
                        
                        myCommand.Parameters["@guid"].Value = guid;
                        myCommand.Parameters["@code"].Value = null;
                        myCommand.Parameters["@del"].Value = true;
                        myCommand.Parameters["@nature"].Value = null;
                        myCommand.ExecuteNonQuery();
                        
                        sqlconn.Close();
                        
                        button6_Click(sender, e);
                        
                    //}
                    //catch (Exception ex)
                    //{
                    //    SqlCommand myCommand = sqlconn.CreateCommand();
                    //    myCommand.Parameters["@guid"].Value = guid;
                    //    myCommand.Parameters["@code"].Value = null;
                    //    myCommand.Parameters["@del"].Value = true;
                    //    myCommand.Parameters["@nature"].Value = null;
                    //    if (sqlconn.State == ConnectionState.Closed) sqlconn.Open();
                    //    myCommand.ExecuteNonQuery();
                    //    sqlconn.Close();
                    //    MessageBox.Show(s);
                    //}
                    sqlcom.Parameters["@guid"].Value = null;
                }
            //}
            //catch (Exception exc)
            //{
            //    MessageBox.Show(s);
            //}
        }

        private void button4_Click(object sender, EventArgs e)
        {

            if (dataGridView1.Rows.Count == 0)
            {
                return;
            }
            cm = (CurrencyManager)dataGridView1.BindingContext[dataGridView1.DataSource];
                cm.SuspendBinding();
                int select = 0;
                int filter = 0;
                foreach (DataGridViewRow r in dataGridView1.Rows)
                {
                    if (r.Visible)
                    {
                        dataGridView1[namechcol, r.Index].Value = true;
                        r.DefaultCellStyle.BackColor = Color.Red;
                        select++;
                        filter++;
                    }
                    else
                    {
                        if (Convert.ToBoolean(dataGridView1[namechcol, r.Index].Value)) select++;
                    }
                }

                cm.ResumeBinding();
                ToolStripItemsUpd(dataGridView1.Rows.Count, select, filter);
        }

        private void button5_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count == 0)
            {
                return;
            }
            cm = (CurrencyManager)dataGridView1.BindingContext[dataGridView1.DataSource];
            cm.SuspendBinding();
            int select = 0;
            int filter = 0;
            if (dataGridView1.DataSource == dt_drug.DefaultView)
            {
                foreach (DataGridViewRow r in dataGridView1.Rows)
                {
                    if (r.Visible)
                    {
                        dataGridView1[namechcol, r.Index].Value = false;
                        r.DefaultCellStyle.BackColor = SystemColors.ControlLightLight;
                        filter++;
                    }
                    if (Convert.ToBoolean(dataGridView1[namechcol, r.Index].Value))
                    {
                        select++;
                    }
                }
            }
            if (dataGridView1.DataSource == dt_drug_tmp.DefaultView)
            {
                dt_drug_tmp.Clear();
            }
            cm.ResumeBinding();
            ToolStripItemsUpd(dataGridView1.Rows.Count, select, filter);
        }

        private void EnterFilter()
        {
            try
            {
                if (textBox12.Text == "") sqlcom.Parameters["@drugcode"].Value = null;
                else sqlcom.Parameters["@drugcode"].Value = Convert.ToInt32(textBox12.Text);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Неверный формат кода товара \t" + ex.ToString());
            }
            sqlcom.Parameters["@drug"].Value = comboBox2.Text;
            sqlcom.Parameters["@country"].Value = comboBox3.Text;
            sqlcom.Parameters["@prod"].Value = comboBox4.Text;
            sqlcom.Parameters["@order"].Value = comboBox5.Text;
            sqlcom.Parameters["@spec"].Value = comboBox6.Text;
            if (radioButton4.Checked) sqlcom.Parameters["@assort"].Value = null;
            else
                if (radioButton5.Checked) sqlcom.Parameters["@assort"].Value = "нет";
                else sqlcom.Parameters["@assort"].Value = "да";
            if (radioButton1.Checked) sqlcom.Parameters["@arch"].Value = null;
            else
                if (radioButton2.Checked) sqlcom.Parameters["@arch"].Value = "да";
                else sqlcom.Parameters["@arch"].Value = "нет";
            sqlcom.Parameters["@categ"].Value = comboBox7.Text;
            sqlcom.Parameters["@dep"].Value = comboBox8.Text;
            sqlcom.Parameters["@mnn"].Value = comboBox9.Text;
            sqlcom.Parameters["@trtech"].Value = comboBox10.Text;

            if (sqlconn.State == ConnectionState.Closed) sqlconn.Open();
            dt_drug.Clear();
            dt_drug.Load(sqlcom.ExecuteReader());
            sqlconn.Close();
            dt_drug.Columns[0].ReadOnly = false;
            dataGridView1.DataSource = dt_drug.DefaultView;
            dataGridView1.Sort(dataGridView1.Columns["Товар"], ListSortDirection.Ascending);

            foreach (DataRow dr in dt_drug.Rows)
            {
                foreach (DataRow dr_t in dt_drug_tmp.Rows)
                {
                    if (dr_t.RowState != DataRowState.Deleted)
                        if (Convert.ToInt32(dr_t["Код"]) == Convert.ToInt32(dr["Код"]))
                        {
                            dr[namechcol] = true;
                        }
                }
            }
            HideShowRows();
        }

        private void button7_Click(object sender, EventArgs e)
        {
            textBox12.Text = "";
            comboBox2.SelectedIndex = -1;
            comboBox3.SelectedIndex = -1;
            comboBox4.SelectedIndex = -1;
            comboBox5.SelectedIndex = -1;
            comboBox6.SelectedIndex = -1;
            comboBox7.SelectedIndex = -1;
            comboBox8.SelectedIndex = -1;
            comboBox9.SelectedIndex = -1;
            comboBox10.SelectedIndex = -1;
        }

        private void textBox12_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter) EnterFilter();
        }

        private void radioButton4_CheckedChanged(object sender, EventArgs e)
        {
            EnterFilter();
        }

        private void button6_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Columns.Count == 0)
            {
                return;
            }
            if (dt_drug_tmp.Rows.Count > 0)
            {
                dt_drug_tmp.Columns[0].ReadOnly = false;
                dataGridView1.DataSource = dt_drug_tmp.DefaultView;
                dataGridView1.Sort(dataGridView1.Columns["Товар"], ListSortDirection.Ascending);
                HideShowRows();
            }
        }

        private ComboBox LoadFiltersCB(SqlConnection con, ComboBox cb)
        {
            try
            {
                SqlCommand myCommand = con.CreateCommand();
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = @"pas_DrugFilterSel";
                if (con.State == ConnectionState.Closed) con.Open();
                DataTable dt = new DataTable();
                dt.Clear();
                dt.Load(myCommand.ExecuteReader());
                con.Close();
                cb.Items.Clear();

                foreach (DataRow dr in dt.Rows)
                    cb.Items.Add(new Item(dr["DrugFiltersName"].ToString(), Convert.ToInt32(dr["DrugFiltersID"].ToString())));
                dt.Clear();
                dt = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            return cb;
        }

        private class Item
        {
            public string Name;
            public int Value;
            public Item(string name, int value)
            {
                Name = name;
                Value = value;
            }
            public override string ToString()
            {
                return Name;
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                Item itm = (Item)comboBox1.SelectedItem;
                sqlcom.Parameters["@filtid"].Value = itm.Value;
                dt_drug_tmp.Clear();
                if (sqlconn.State == ConnectionState.Closed) sqlconn.Open();
                dt_drug_tmp.Load(sqlcom.ExecuteReader());
                sqlconn.Close();
                textBox1.Text = "";
                dt_drug_tmp.Columns[0].ReadOnly = false;
                dataGridView1.DataSource = dt_drug_tmp.DefaultView;
                dataGridView1.Sort(dataGridView1.Columns["Товар"], ListSortDirection.Ascending);
                HideShowRows();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            sqlcom.Parameters["@filtid"].Value = null;
        }

        private void GetFilterTable(object[] obj)
        {
            SqlCommand myCommand = sqlconn.CreateCommand();
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = @"pas_GuidNatureCodeINS";
            myCommand.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
            myCommand.Parameters.Add("@code", SqlDbType.Int);
            myCommand.Parameters.Add("@del", SqlDbType.Bit);
            myCommand.Parameters.Add("@nature", SqlDbType.VarChar, 5);

            sqlconn.Open();

            myCommand.Parameters["@guid"].Value = guid;
            myCommand.Parameters["@code"].Value = null;
            myCommand.Parameters["@del"].Value = true;
            myCommand.Parameters["@nature"].Value = null;
            myCommand.ExecuteNonQuery();

            guid = Guid.NewGuid();

            if (obj.Length > 0)
                foreach (object ob in obj)
                {
                    myCommand.Parameters["@guid"].Value = guid;
                    myCommand.Parameters["@code"].Value = Convert.ToInt32(ob); ;
                    myCommand.Parameters["@del"].Value = false;
                    myCommand.Parameters["@nature"].Value = "drug";
                    myCommand.ExecuteNonQuery();
                }

            sqlcom.Parameters["@guid"].Value = guid;
            dt_drug_tmp.Clear();
            dt_drug_tmp.Load(sqlcom.ExecuteReader());
            dt_drug_tmp.Columns[0].ReadOnly = false;
            myCommand.Parameters["@guid"].Value = guid;
            myCommand.Parameters["@code"].Value = null;
            myCommand.Parameters["@del"].Value = true;
            myCommand.Parameters["@nature"].Value = null;
            myCommand.ExecuteNonQuery();

            sqlconn.Close();
        }

        public struct StorProcParam
        {
            public string Name;
            public SqlDbType Type;
            public int TypeSize;
            public object Value;
        }

        public StorProcParam StorProcParamIns(string Name, SqlDbType Type, int TypeSize, object Value)
        {
            StorProcParam spp;
            spp.Name = Name;
            spp.Type = Type;
            spp.TypeSize = TypeSize;
            spp.Value = Value;

            return spp;
        }

        public SqlCommand StorProc(SqlCommand com, SqlConnection con, string sp_name, StorProcParam[] param)
        {
            com = new SqlCommand();
            com = con.CreateCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = sp_name;
            com.CommandTimeout = 300;
            foreach (StorProcParam spp in param)
            {
                if (spp.TypeSize != -1)
                    com.Parameters.Add(spp.Name, spp.Type, spp.TypeSize);
                else
                    com.Parameters.Add(spp.Name, spp.Type);
                com.Parameters[spp.Name].Value = spp.Value;
            }
            return com;
        }

        private SqlCommand SelDrugs(SqlCommand com, SqlConnection con)
        {
            string sp_name = @"pas_AllDrugsSel";
            StorProcParam[] param = new StorProcParam[15];

            param[0] = StorProcParamIns("@drugcode", SqlDbType.Int, -1, null);
            param[1] = StorProcParamIns("@drug", SqlDbType.VarChar, 20, null);
            param[2] = StorProcParamIns("@country", SqlDbType.VarChar, 20, null);
            param[3] = StorProcParamIns("@prod", SqlDbType.VarChar, 20, null);
            param[4] = StorProcParamIns("@order", SqlDbType.VarChar, 20, null);

            param[5] = StorProcParamIns("@spec", SqlDbType.VarChar, 20, null);
            param[6] = StorProcParamIns("@assort", SqlDbType.VarChar, 20, null);
            param[7] = StorProcParamIns("@arch", SqlDbType.VarChar, 20, null);
            param[8] = StorProcParamIns("@categ", SqlDbType.VarChar, 20, null);
            param[9] = StorProcParamIns("@dep", SqlDbType.VarChar, 20, null);

            param[10] = StorProcParamIns("@mnn", SqlDbType.VarChar, 20, null);
            param[11] = StorProcParamIns("@trtech", SqlDbType.VarChar, 20, null);
            param[12] = StorProcParamIns("@guid", SqlDbType.UniqueIdentifier, -1, null);
            param[13] = StorProcParamIns("@filtid", SqlDbType.Int, -1, null);
            param[14] = StorProcParamIns("@brandid", SqlDbType.Int, -1, null);

            com = StorProc(com, con, sp_name, param);

            return com;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (dt_drug_tmp.Rows.Count > 0)
            {
                if (textBox2.Text == "")
                    MessageBox.Show("Введите названние фильтра");

                else
                {
                    bool fl = false;
                    for (int i = 0; i < comboBox1.Items.Count; i++)
                        if (comboBox1.Items[i].ToString() == textBox2.Text) fl = true;

                    if (fl) MessageBox.Show("Такой фильтр уже существует");
                    else
                    {
                        InsUserFilter(sqlconn, dt_drug_tmp, textBox2.Text);
                        LoadFiltersCB(sqlconn, comboBox1);
                    }
                }
            }
            else MessageBox.Show("Не выбрано ни одной позиции");

        }


        private void InsUserFilter(SqlConnection con, DataTable tb, string filter)
        {
            try
            {
                SqlCommand myCommand = con.CreateCommand();
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = @"pas_GuidNatureCodeINS";
                myCommand.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
                myCommand.Parameters.Add("@code", SqlDbType.Int);
                myCommand.Parameters.Add("@del", SqlDbType.Bit);
                myCommand.Parameters.Add("@nature", SqlDbType.VarChar, 5);

                if (sqlconn.State == ConnectionState.Closed) con.Open();

                myCommand.Parameters["@guid"].Value = guid;
                myCommand.Parameters["@code"].Value = null;
                myCommand.Parameters["@del"].Value = true;
                myCommand.Parameters["@nature"].Value = null;
                myCommand.ExecuteNonQuery();

                guid = Guid.NewGuid();

                if (tb != null)
                    foreach (DataRow dr in tb.Rows)
                    {
                        myCommand.Parameters["@guid"].Value = guid;
                        myCommand.Parameters["@code"].Value = Convert.ToInt32(dr["Код"]); ;
                        myCommand.Parameters["@del"].Value = false;
                        myCommand.Parameters["@nature"].Value = "drug";
                        myCommand.ExecuteNonQuery();
                    }

                SqlCommand inscomm = con.CreateCommand();
                inscomm.CommandType = CommandType.StoredProcedure;
                inscomm.CommandText = "pas_DrugFilterInsDel";
                inscomm.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
                inscomm.Parameters.Add("@username", SqlDbType.VarChar, 50);
                inscomm.Parameters.Add("@del", SqlDbType.Bit);
                inscomm.Parameters.Add("@filtername", SqlDbType.VarChar, 50);

                inscomm.Parameters["@guid"].Value = guid;
                inscomm.Parameters["@username"].Value = null;
                inscomm.Parameters["@del"].Value = false;
                inscomm.Parameters["@filtername"].Value = filter;
                inscomm.ExecuteNonQuery();
                MessageBox.Show("Фильтр успешно добавлен");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            con.Close();
        }

        private void button3_Click(object sender, EventArgs e)
        {

        }

        private void textBox12_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!(Char.IsDigit(e.KeyChar)) && !((e.KeyChar == '.') && (textBox1.Text.IndexOf(".") == -1) && (textBox1.Text.Length != 0)))
            {
                if (e.KeyChar != (char)Keys.Back)
                {
                    e.Handled = true;
                }
            }
        }

        private void button8_Click(object sender, EventArgs e)
        {
            EnterFilter();
        }

        //private SqlCommand SelBrand(SqlCommand com, SqlConnection con,bool drugs)
        //{
        //    string sp_name = @"pas_AllDrugsSel";
        //    StorProcParam[] param = new StorProcParam[14];

        //    param[0] = StorProcParamIns("@drugs", SqlDbType.Bit, -1, drugs);
        //    com = StorProc(com, con, sp_name, param);

        //    return com;
        //}

        private TreeView LoadTreeView(TreeView tree)
        {
            dt_tree = null;
            dt_tree = new DataTable();
            string sp_name = @"pas_BrandTreeSel";
            SqlCommand comm = new SqlCommand();
            comm.CommandType = CommandType.StoredProcedure;
            comm.Connection = sqlconn;
            comm.CommandText = sp_name;
            comm.Parameters.AddWithValue("@drugs", 0);

            if (sqlconn.State == ConnectionState.Closed) sqlconn.Open();
            dt_tree.Load(comm.ExecuteReader());

            TreeNode tn = new TreeNode();
            DataView dv = new DataView(dt_tree);
            DataTable dt_dis = dv.ToTable(true, "BrandName1");
            foreach (DataRow dr in dt_dis.Rows)
            {
                TreeNode tn1 = new TreeNode();
                tn1.Text = dr[0].ToString();
                treeView1.Nodes.Add(tn1);
            }
            foreach (TreeNode treenode in tree.Nodes)
            {
                string[] selparamval = new string[1];
                selparamval[0] = treenode.Text;
                TreeNodeLoad(2, selparamval, treenode, dt_tree);
            }

            return tree;
        }

        private TreeNode TreeNodeLoad(int col, string[] selparamval, TreeNode node, DataTable table)
        {
            if (col <= 6)
            {
                string[] param = new string[] { };
                string selparam = "";
                for (int i = 0; i < col; i++)
                {
                    if (i < 5)
                    {
                        Array.Resize(ref param, param.Length + 1);
                        param[param.Length - 1] = "BrandName" + (i + 1).ToString();
                        if (i < col - 1)
                        {
                            if (selparam != "") selparam += " and ";
                            selparam += param[i] + " = '" + selparamval[i] + "'";
                        }
                    }

                }
                string[] newselparam = selparamval;
                Array.Resize(ref newselparam, newselparam.Length + 1);
                foreach (DataRow dr in (new DataView(table)).ToTable(true, param).Select(selparam))
                {
                    if (col < 6)
                    {
                        if (dr[param[col - 1]].ToString() != "")
                        {
                            int ind = node.Nodes.Add(dr[param[col - 1]].ToString()).Index;
                            newselparam[newselparam.Length - 1] = node.Nodes[ind].Text;
                            TreeNodeLoad(col + 1, newselparam, node.Nodes[ind], table);
                        }
                        if (dr[param[col - 1]].ToString() == "")
                        {
                            string s = dt_tree.Select(selparam)[0]["BrandID"].ToString();
                            node.ToolTipText = dt_tree.Select(selparam)[0]["BrandID"].ToString();
                        }
                    }
                    else
                    {
                        node.ToolTipText = dt_tree.Select(selparam)[0]["BrandID"].ToString();
                    }
                }
            }
            return node;
        }

        private void treeView1_DoubleClick(object sender, EventArgs e)
        {
            if (treeView1.SelectedNode.ToolTipText != "")
            {
                try
                {
                    sqlcom.Parameters["@brandid"].Value = Convert.ToInt32(treeView1.SelectedNode.ToolTipText);
                    dt_drug_tmp.Clear();
                    if (sqlconn.State == ConnectionState.Closed) sqlconn.Open();
                    dt_drug_tmp.Load(sqlcom.ExecuteReader());
                    sqlconn.Close();
                    textBox1.Text = "";
                    dt_drug_tmp.Columns[0].ReadOnly = false;
                    dataGridView1.DataSource = dt_drug_tmp.DefaultView;
                    dataGridView1.Sort(dataGridView1.Columns["Товар"], ListSortDirection.Ascending);
                    HideShowRows();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                sqlcom.Parameters["@brandid"].Value = null;
            }
        }

        private void button9_Click(object sender, EventArgs e)
        {
            //- Загрузить каталог списков
            dt_tree.Clear();
            this.Text += " Загрузка каталога";
            LoadTreeView(treeView1);
            this.Text = Text.Replace(" Загрузка каталога", ""); 
        }
    }
}
